﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using Maticsoft.DBUtility;

namespace ElingOA6_Admin_DSJCJ.DAL
{
    /// <summary>
    /// Fwmmyhsr数据类
    /// </summary>
    public class Fwmmyhsr
    {

         /// <summary>
        /// 增加一条数据
        /// </summary>
        /// <creator>chenly</creator>
        /// <date>2013-8-1</date>
        /// <param name="model">model对象</param>
        /// <returns>obj对象的个数</returns>
        public decimal Add(ElingOA6_Admin_DSJCJ.Model.Fwmmyhsr model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into [nc].nc_fwmmyhsr(");
            strSql.Append("khyh,srsj,srje,srbz)");
            strSql.Append(" values (");
            strSql.Append("@khyh,@srsj,@srje,@srbz)");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
					new SqlParameter("@khyh", SqlDbType.VarChar,10),
					new SqlParameter("@srsj", SqlDbType.DateTime),
					new SqlParameter("@srje", SqlDbType.Decimal,9),
					new SqlParameter("@srbz", SqlDbType.VarChar,100)};
            parameters[0].Value = model.khyh;
            parameters[1].Value = model.srsj;
            parameters[2].Value = model.srje;
            parameters[3].Value = model.srbz;

            object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToDecimal(obj);
            }
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        /// <creator>chenly</creator>
        /// <date>2013-8-1</date>
       /// <param name="id">主键</param>
       /// <returns>是否删除</returns>
        public bool Delete(decimal id)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from [nc].nc_fwmmyhsr ");
            strSql.Append(" where id=@id");
            SqlParameter[] parameters = {
					new SqlParameter("@id", SqlDbType.Decimal)
			};
            parameters[0].Value = id;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 批量删除
        /// <creator>chenly</creator>
        /// <date>2013-8-1</date>
        /// </summary>
        /// <param name="idArray">数组编号</param>
        public void DeleteMany(string[] idArray)
        {
            StringBuilder strSql = new StringBuilder();
            CommandInfo cmds = new CommandInfo();
            List<CommandInfo> cmdList = new List<CommandInfo>();

            if (idArray.Length > 0)
            {
                strSql = new StringBuilder();
                strSql.Append("delete from [nc].nc_fwmmyhsr ");
                strSql.Append(" where id=@id ");

                string strSqlAd = strSql.ToString();

                foreach (string idNo in idArray)
                {
                    cmds = new CommandInfo();

                    SqlParameter[] parameters = {
					    new SqlParameter("@id", SqlDbType.Decimal)	};

                    parameters[0].Value = idNo;

                    cmds.CommandText = strSqlAd;
                    cmds.Parameters = parameters;
                    cmdList.Add(cmds);
                }

            }

            DbHelperSQL.ExecuteSqlTranWithString(cmdList);

        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        /// <creator>chenly</creator>
        /// <date>2013-8-1</date>
        /// <param name="model">model对象</param>
        /// <returns>是否更新</returns>
        public bool Update(ElingOA6_Admin_DSJCJ.Model.Fwmmyhsr model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update [nc].nc_fwmmyhsr set ");
            strSql.Append("khyh=@khyh,");
            strSql.Append("srsj=@srsj,");
            strSql.Append("srje=@srje,");
            strSql.Append("srbz=@srbz");
            strSql.Append(" where id=@id");
            SqlParameter[] parameters = {
					new SqlParameter("@khyh", SqlDbType.VarChar,10),
					new SqlParameter("@srsj", SqlDbType.DateTime),
					new SqlParameter("@srje", SqlDbType.Decimal,9),
					new SqlParameter("@srbz", SqlDbType.VarChar,100),
					new SqlParameter("@id", SqlDbType.Decimal,9)};
            parameters[0].Value = model.khyh;
            parameters[1].Value = model.srsj;
            parameters[2].Value = model.srje;
            parameters[3].Value = model.srbz;
            parameters[4].Value = model.id;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        /// <creator>chenly</creator>
        /// <date>2013-8-1</date>
        /// <param name="id">主键</param>
        /// <returns>实体对象</returns>
        public Model.Fwmmyhsr GetModel(decimal id)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 id, khyh, srbz, srje, srsj from [nc].nc_fwmmyhsr ");
            strSql.Append(" where id=@id ");
            SqlParameter[] parameters = {
					new SqlParameter("id", SqlDbType.Decimal)};
            parameters[0].Value = id;

            Model.Fwmmyhsr model = new Model.Fwmmyhsr();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["id"] != null && ds.Tables[0].Rows[0]["id"].ToString() != "")
                {
                    model.id = decimal.Parse(ds.Tables[0].Rows[0]["id"].ToString());
                }
                if (ds.Tables[0].Rows[0]["khyh"] != null && ds.Tables[0].Rows[0]["khyh"].ToString() != "")
                {
                    model.khyh = ds.Tables[0].Rows[0]["khyh"].ToString();
                }
                if (ds.Tables[0].Rows[0]["srbz"] != null && ds.Tables[0].Rows[0]["srbz"].ToString() != "")
                {
                    model.srbz = ds.Tables[0].Rows[0]["srbz"].ToString();
                }
                if (ds.Tables[0].Rows[0]["srje"] != null && ds.Tables[0].Rows[0]["srje"].ToString() != "")
                {
                    model.srje = decimal.Parse(ds.Tables[0].Rows[0]["srje"].ToString());
                }
                if (ds.Tables[0].Rows[0]["srsj"] != null && ds.Tables[0].Rows[0]["srsj"].ToString() != "")
                {
                    model.srsj = DateTime.Parse(ds.Tables[0].Rows[0]["srsj"].ToString());
                }

                return model;
            }
            else
            {
                return null;
            }
        }
        /// <summary>
        /// 获取银行收支情况
        /// </summary>
        /// <param name="stratDate">开始时间</param>
        /// <param name="endDate">结束时间</param>
        /// <returns></returns>
        public DataSet GetBankPayBalance(string stratDate, string endDate) 
        { 
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" SELECT a.khyh AS '银行',ISNULL(a.ncs,0)+ ISNULL(c.srjeSum,0)-ISNULL(b.ybtseSum,0) AS '期初数',");
            strSql.Append(" ISNULL(d.ybtseSum,0) AS '本期支出',ISNULL(e.srjeSum,0) AS '本期收入' FROM dbo.nc_fwmmyh a ");
            strSql.Append(" LEFT JOIN( ");
            strSql.Append(" SELECT khyh,ISNULL(SUM(ybtse),0) AS ybtseSum FROM [nc].[nc_fwmmszbt] WHERE DATEDIFF(DAY, btrq,'" + stratDate + "')>0 ");
            strSql.Append(" GROUP BY khyh) b ON a.khyh = b.khyh");
            strSql.Append(" LEFT JOIN (");
            strSql.Append(" SELECT  khyh,ISNULL(SUM(srje),0) AS srjeSum FROM dbo.nc_fwmmyhsr WHERE DATEDIFF(DAY,srsj,'" + stratDate + "')>0 ");
            strSql.Append(" GROUP BY khyh) c ON a.khyh = c.khyh");
            strSql.Append(" LEFT JOIN (");
            strSql.Append(" SELECT  khyh,ISNULL(SUM(ybtse),0) AS ybtseSum,COUNT(*) AS counts");
            strSql.Append(" FROM [nc].[nc_fwmmszbt] WHERE DATEDIFF(DAY, btrq,'" + endDate + "')>=0 AND DATEDIFF(DAY, btrq,'" + stratDate + "')<0");
            strSql.Append(" GROUP BY khyh) AS d ON a.khyh = d.khyh");
            strSql.Append(" LEFT JOIN (");
            strSql.Append(" SELECT khyh,ISNULL(SUM(srje),0) AS srjeSum FROM dbo.nc_fwmmyhsr WHERE DATEDIFF(DAY, srsj,'" + endDate + "')>=0 AND DATEDIFF(DAY, srsj,'" + stratDate + "')<0");
            strSql.Append(" GROUP BY khyh) e ON a.khyh = e.khyh  ");
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获取财政补贴情况
        /// </summary>
        /// <param name="stratDate">开始时间</param>
        /// <param name="endDate">结束时间</param>
        /// <returns></returns>
        public DataSet GetFiscalSubsidies(string stratDate, string endDate)
        {
            string yearDate = DateTime.Parse(endDate.ToString()).AddYears(-1).Year.ToString() + "-12" + "-25"; ;
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT DISTINCT d.sz AS '税种',d.khyh AS '开户银行',ISNULL(a.ybtseSum,0) AS '历年累计金额',ISNULL(a.counts,0) AS '历年累计户数',ISNULL(b.ybtseSum,0) AS '本年累计金额',ISNULL(b.counts,0) AS '本年累计户数',ISNULL(c.ybtseSum,0) AS '本期金额',ISNULL(c.counts,0) AS '本期户数' FROM dbo.nc_fwmmszbt d LEFT JOIN ");
            strSql.Append(" (SELECT khyh,sz,SUM(ybtse) AS ybtseSum,COUNT(*)  AS counts FROM  dbo.nc_fwmmszbt ");
            strSql.Append(" WHERE DATEDIFF(DAY, btrq,'" + endDate + "')>=0");
            strSql.Append(" GROUP BY sz,khyh ) a ON d.khyh = a.khyh AND d.sz = a.sz LEFT JOIN ");
            strSql.Append(" (SELECT khyh,sz,SUM(ybtse)AS ybtseSum,COUNT(*) AS counts FROM  dbo.nc_fwmmszbt ");
            strSql.Append(" WHERE DATEDIFF(day, btrq, '" + endDate + "')>=0 AND DATEDIFF(day, btrq, '" + yearDate + "') <=0");
            strSql.Append(" GROUP BY sz,khyh) b ON d.khyh = b.khyh AND d.sz = b.sz");
            strSql.Append(" LEFT JOIN (");
            strSql.Append(" SELECT khyh,sz,SUM(ybtse)AS ybtseSum,COUNT(*) AS counts FROM  dbo.nc_fwmmszbt ");
            strSql.Append(" WHERE DATEDIFF(DAY, btrq,'" + endDate + "')>=0 AND DATEDIFF(DAY, btrq,'" + stratDate + "')<0");
            strSql.Append(" GROUP BY sz,khyh) c ON d.khyh = c.khyh AND d.sz = c.sz");
            return DbHelperSQL.Query(strSql.ToString());
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="yyyy"></param>
        /// <param name="mm"></param>
        /// <returns></returns>
        public DataSet GetNextDate(string yyyy, string mm) 
        {
            string yyyymm = yyyy + "-" + mm + "-05";
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select convert(char(10),dateadd(mm,1,'" + yyyymm + "'),112)  as day");
            return DbHelperSQL.Query(strSql.ToString());
        }

        public DataSet GetPreDate(string yyyy, string mm)
        {
            string yyyymm = yyyy + "-" + mm + "-20";
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select convert(char(10),dateadd(mm,-1,'" + yyyymm + "'),112)  as day");
            return DbHelperSQL.Query(strSql.ToString());
        }


    }
}
